#!/usr/bin/env python
# coding: utf-8

# In[1]:


import pandas as pd
from pandas import DataFrame as df


# In[2]:


data=pd.read_csv('大数据分析综合实训-附件.csv',encoding='gbk')
data


# In[3]:


data.head()


# In[4]:


data.shape  #查看数据行列


# In[5]:


data.info()  #检查字段类型


# In[6]:


data.isnull().sum() #统计每个字段缺失数目


# In[7]:


data.dropna(inplace=True)


# In[8]:


data.shape


# In[9]:


# 对data中销售日期进行时间格式转换，coerce将无效解析设置为NaT
data.loc[:,'销售日期'] = pd.to_datetime(data.loc[:,'销售日期'].astype(str), format='%Y-%m-%d', errors='coerce')


# In[ ]:





# In[10]:


#对数值型数据做统计，了解数据分布
data.describe()


# In[11]:


new_data=pd.DataFrame()


# In[12]:


data_big = data[['大类名称','销售金额']]
data_big.head()


# In[13]:


# 大类名称出现的频次
data_big['大类名称'].value_counts(dropna=False)


# In[14]:


data_big = data_big.groupby('大类名称').sum()
data_big.rename(columns = {'销售金额':'销售金额总和'}, inplace=True)
data_big


# In[15]:


# 提取是否促销信息和销售金额
data_promotion = data[['中类名称','是否促销','销售金额']]
data_promotion


# In[16]:


data_promotion_y =data_promotion[data_promotion['是否促销']=='是']
data_promotion_y


# In[17]:


data_promotion_n =data_promotion[data_promotion['是否促销']=='否']
data_promotion_n


# In[18]:


data_customer = data[['顾客编号','销售日期']]
data_customer


# In[19]:


pd.to_datetime(data['销售日期'],errors='ignore')


# In[20]:


data.info()


# In[27]:


# 根据顾客编号列，求出每位顾客每月的消费次数
data_month1_times = data_month1['顾客编号'].value_counts(dropna=False)
data_month2_times = data_month2['顾客编号'].value_counts(dropna=False)
data_month3_times = data_month3['顾客编号'].value_counts(dropna=False)
data_month4_times = data_month4['顾客编号'].value_counts(dropna=False)

data_month1_times = pd.DataFrame({'顾客编号':data_month1_times.index, '次数':data_month1_times.values})
data_month2_times = pd.DataFrame({'顾客编号':data_month2_times.index, '次数':data_month2_times.values})
data_month3_times = pd.DataFrame({'顾客编号':data_month3_times.index, '次数':data_month3_times.values})
data_month4_times = pd.DataFrame({'顾客编号':data_month4_times.index, '次数':data_month4_times.values})


# In[28]:


# 将四个消费次数表连接
data_month12_times = pd.merge(data_month1_times, data_month2_times, how='left', left_on='顾客编号', right_on='顾客编号')
data_month34_times = pd.merge(data_month3_times, data_month4_times, how='left', left_on='顾客编号', right_on='顾客编号')
data_month1234_times = pd.merge(data_month12_times, data_month34_times, how='left', left_on='顾客编号', right_on='顾客编号')
data_month1234_times.columns = list(['顾客编号', '1月消费次数', '2月消费次数', '3月消费次数', '4月消费次数'])
data_month1234_times.fillna(0, inplace=True)
data_month1234_times


# In[29]:


data.head()


# In[30]:


# 提取顾客编号、销售日期、销售金额
data_customer = data[['顾客编号','销售日期','销售金额']]
data_customer.head()


# In[31]:


# 根据销售日期列获取消费月份列
data_customer['月份'] = [x.month for x in data_customer['销售日期']]
data_customer.drop(['销售日期'], axis=1, inplace=True)
data_customer.head()


# In[41]:


# 拆分出四个月的表
data_month1 = data_customer.loc[data_customer['月份'] == 1,:]
data_month2 = data_customer.loc[data_customer['月份'] == 2,:]
data_month3 = data_customer.loc[data_customer['月份'] == 3,:]
data_month4 = data_customer.loc[data_customer['月份'] == 4,:]


# In[42]:


#根据顾客编号列分组，求出每位顾客每月的消费额
data_month1_cost = data_month1.groupby('顾客编号').sum()
data_month1_cost.drop(['月份'], axis=1, inplace=True)
data_month2_cost = data_month2.groupby('顾客编号').sum()
data_month2_cost.drop(['月份'], axis=1, inplace=True)
data_month3_cost = data_month3.groupby('顾客编号').sum()
data_month3_cost.drop(['月份'], axis=1, inplace=True)
data_month4_cost = data_month4.groupby('顾客编号').sum()
data_month4_cost.drop(['月份'], axis=1, inplace=True)


# In[43]:


# 将四个消费额表连接，并将NaN替换成0
data_month1234_cost = pd.concat([data_month1_cost, data_month2_cost, data_month3_cost, data_month4_cost], axis=1, ignore_index=True)
data_month1234_cost.columns = list(['1月消费额', '2月消费额', '3月消费额', '4月消费额'])
data_month1234_cost.fillna(0, inplace=True)
data_month1234_cost.head()


# In[47]:


# 提取销售日期、大类名称、销售金额
data_new = data[['大类名称','销售日期','销售金额']]
data_new.head()


# In[48]:


# 添加一列作为月份
data_new['月份'] = [x.month for x in data_new['销售日期']]


# In[49]:


data_new


# In[50]:


data_new['大类名称'].value_counts(dropna=False)


# In[51]:


data_month1 = data_new.loc[data_new['月份'] == 1,:]
data_month2 = data_new.loc[data_new['月份'] == 2,:]
data_month3 = data_new.loc[data_new['月份'] == 3,:]
data_month4 = data_new.loc[data_new['月份'] == 4,:]


# In[52]:


# 根据大类名称分组，求各大类商品的每月销售金额
data_month_cost1 = data_month1.groupby('大类名称').sum()[['销售金额']]
data_month_cost2 = data_month2.groupby('大类名称').sum()[['销售金额']]
data_month_cost3 = data_month3.groupby('大类名称').sum()[['销售金额']]
data_month_cost4 = data_month4.groupby('大类名称').sum()[['销售金额']]


# In[53]:


from matplotlib import pyplot as plt
plt.rcParams['font.sans-serif']=['SimHei']
plt.rcParams['axes.unicode_minus']=False


# In[54]:


# 画饼图

# 设置图框的大小
fig = plt.figure(figsize=(10,8))
# 前两个1表示共有1*1个子图，最后一个1表示第1个子图
ax = fig.add_subplot(1,1,1)

# 绘制饼图，textprops={'fontproperties':font}显示中文
plt.pie(x=data_month_cost1.values,
        labels=data_month_cost1.index,
        autopct='%.1f%%',
        shadow=False,
        startangle=90,
        center = (3,3))

# 添加标题，fontproperties=font显示中文
plt.title("1月份销售金额")
# 显示图例，prop=font显示中文
plt.legend()
# 饼图保持圆形
plt.axis('equal')
# 显示图像
plt.show()


# In[ ]:




